//
//  YNUserTreeRelation.m
//  Messenger
//
//  Created by OC on 15/1/15.
//  Copyright (c) 2015年 李健. All rights reserved.
//
//  用户组织机构关系实体类
//

#import "YNUserTreeRelation.h"
#import "YNUser.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "FMDatabaseAdditions.h"
#import "XMPPDataSecurity.h"
#import "YNIMTool.h"
#import "YNIMFrameworkMacro.h"

@implementation YNUserTreeRelation

-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
    if (!value) {
        if ([key isEqualToString:@"ordId"]) {
            self.ordId=0;
        }else if ([key isEqualToString:@"removeTag"]){
            self.removeTag=0;
        }
    }
}

+ (NSString *)ID
{
    return @"ID";
}
+ (NSString *)ordId
{
    return @"ordId";
}
+ (NSString *)orgNo
{
    return @"orgNo";
}
+ (NSString *)userNo
{
    return @"userNo";
}
+ (NSString *)relationType
{
    return @"relationType";
}
+ (NSString *)removeTag
{
    return @"removeTag";
}

+ (NSString *)tableName
{
    return @"UserTreeRelation";
}

- (NSArray *)insertParam
{
    return @[[YNIMTool nilFilter:self.orgNo],
             [YNIMTool nilFilter:self.userNo],
             [NSNumber numberWithInt:self.ordId],
             [YNIMTool nilFilter:self.relationType],
             [NSNumber numberWithInt:self.removeTag]];
}

- (NSArray *)updateParam
{
    return @[[NSNumber numberWithInt:self.ordId],
             [YNIMTool nilFilter:self.relationType],
             [NSNumber numberWithInt:self.removeTag],
             [YNIMTool nilFilter:self.orgNo],
             [YNIMTool nilFilter:self.userNo]];
}

//创建用户与组织机构关系表
+ (BOOL)createInDatabase:(FMDatabase *)db
{
    if ([db tableExists:[YNUserTreeRelation tableName]])
    {
        return YES;
    }
    
    NSString *createUserTreeRelationSql = [NSString stringWithFormat:@"%@%@(%@ INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %@ varchar(36), %@ varchar(36), %@ INTEGER, %@ varchar(20), %@ INTEGER)",CREATE_TABLE,
                                           [YNUserTreeRelation tableName],
                                           [YNUserTreeRelation ID],
                                           [YNUserTreeRelation orgNo],
                                           [YNUserTreeRelation userNo],
                                           [YNUserTreeRelation ordId],
                                           [YNUserTreeRelation relationType],
                                           [YNUserTreeRelation removeTag]];
    
    BOOL result = [db executeUpdate:createUserTreeRelationSql];
    return result;
}

//插入用户与组织机构关系信息
- (BOOL)insertInDatabase:(FMDatabase *)db
{
    
    NSString *fieldString = [NSString stringWithFormat:@"%@,%@,%@,%@,%@",
                             [YNUserTreeRelation orgNo],
                             [YNUserTreeRelation userNo],
                             [YNUserTreeRelation ordId],
                             [YNUserTreeRelation relationType],
                             [YNUserTreeRelation removeTag]];
    
    NSString *insertUserTreeRelationSql = [NSString stringWithFormat:@"%@%@ (%@) VALUES (?,?,?,?,?)",INSERT_TABLE,[YNUserTreeRelation tableName],fieldString];
    
    BOOL result = [db executeUpdate:insertUserTreeRelationSql withArgumentsInArray:[self insertParam]];
    
    return result;
}

//判断用户机构关系是否存在
- (BOOL)isUserTreeRelationExistInDatabase:(FMDatabase *)db
{
    NSString *queryOrgTreeSql = [NSString stringWithFormat:@"SELECT *FROM %@ WHERE %@ = ? AND %@ = ?",
                                 [YNUserTreeRelation tableName],
                                 [YNUserTreeRelation orgNo],
                                 [YNUserTreeRelation userNo]];
    
    FMResultSet *rs = [db executeQuery:queryOrgTreeSql,self.orgNo,self.userNo];
    
    BOOL isUserTreeRelationExist = [rs next];
    return isUserTreeRelationExist;
}


//更新用户机构关系信息
- (BOOL)updateInDatabase:(FMDatabase *)db
{
    NSString *updateUserTreeRelationSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = ?, %@ = ?, %@ = ? WHERE %@ = ? AND %@ = ?",
                                           [YNUserTreeRelation tableName],
                                           [YNUserTreeRelation ordId],
                                           [YNUserTreeRelation relationType],
                                           [YNUserTreeRelation removeTag],
                                           [YNUserTreeRelation orgNo],
                                           [YNUserTreeRelation userNo]];
    
    BOOL result = [db executeUpdate:updateUserTreeRelationSql withArgumentsInArray:[self updateParam]];
    return result;
}


//根据orgNo查询直属User
+ (NSArray *)queryUserByOrgNo:(NSString *)orgNo database:(FMDatabase *)db
{
    NSMutableArray*(^queryUserBlock)(NSString*orgNo,NSString*querySql,FMDatabase*db)=^NSMutableArray*(NSString*orgNo,NSString*querySql,FMDatabase*db){
        NSMutableArray*userArray=[NSMutableArray array];
        FMResultSet *rs=[db executeQuery:querySql,orgNo];
        while ([rs next]) {
            YNUser*user=[[YNUser alloc]init];
            if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
                [user setValuesForKeysWithDictionary:[rs resultDictionary]];
                user.telephone=[XMPPDataSecurity dataDecryptionBy:user.telephone];
                user.email=[XMPPDataSecurity dataDecryptionBy:user.email];
                [userArray addObject:user];
            }
        }
        return userArray;
    };
    
    NSMutableArray*allUserArray=[NSMutableArray array];
    NSString*userId=[YNIMmanager sharedInstance].userImId;
    //先查询自己
    [allUserArray addObjectsFromArray:queryUserBlock(orgNo,[NSString stringWithFormat:@"SELECT u.* FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo = ? AND r.removeTag = 0 AND u.userNo = '%@' ORDER BY r.ordId",userId],db)];
    //查询在线的人
    [allUserArray addObjectsFromArray:queryUserBlock(orgNo,[NSString stringWithFormat:@"SELECT u.* FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo = ? AND r.removeTag = 0 AND (u.pcStatus = 'online' OR u.phoneStatus = 'online') AND u.userNo <> '%@' ORDER BY r.ordId",userId],db)];
    //查询非在线人数
    [allUserArray addObjectsFromArray:queryUserBlock(orgNo,[NSString stringWithFormat:@"SELECT u.* FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo = ? AND r.removeTag = 0 AND (u.pcStatus IS NULL AND u.phoneStatus IS NULL) AND u.userNo <> '%@' ORDER BY r.ordId",userId],db)];
    return allUserArray;
}

//根据orgNo查询直属User，不包含老师
+ (NSArray *)queryUserNotTeacherByOrgNo:(NSString *)orgNo database:(FMDatabase *)db
{
    
    //2016-11-6jc修改
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT u.* FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND u.userType = 2 AND r.orgNo = ? AND r.removeTag = 0 ORDER BY r.ordId "];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql,orgNo];
    
    NSMutableArray *userArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        user.telephone=[XMPPDataSecurity dataDecryptionBy:user.telephone];
        user.email=[XMPPDataSecurity dataDecryptionBy:user.email];
        [userArray addObject:user];
    }
    
    [rs close];
    return userArray;
}



//根据orgNo查询直属User数量
+(NSInteger)queryUserCountByOrgNos:(NSString *)orgNos database:(FMDatabase *)db
{
    //2016-11-6jc修改
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT * FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo in (%@) AND r.removeTag = 0 ORDER BY r.ordId",orgNos];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql];
    
    NSMutableSet *userArray = [NSMutableSet set];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        [userArray addObject:user.userNo];
    }
    [rs close];
    
    return userArray.count;
}

//根据orgNo查询下面的所有User对象 noTeacher:查询出的对象里是否不包含老师
+(NSArray *)queryAllUserByOrgNos:(NSString *)orgNos database:(FMDatabase *)db isNoTeacher:(BOOL)noTeacher
{
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT *FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo in (%@) AND r.removeTag = 0 ORDER BY r.ordId",orgNos];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql];
    
    NSMutableArray *userArray = [NSMutableArray array];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        user.telephone=[XMPPDataSecurity dataDecryptionBy:user.telephone];
        user.email=[XMPPDataSecurity dataDecryptionBy:user.email];
        if (noTeacher) {
            if (user.userType!=1) {
                [userArray addObject:user];
            }
        }else{
            [userArray addObject:user];
        }
        
    }
    
    [rs close];
    return userArray;
}

//根据orgNo查询直属User在线人数数量
+(NSInteger)queryOnlineUserCountByOrgNos:(NSString *)orgNos database:(FMDatabase *)db
{
    //2016-11-6jc修改
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT * FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND r.orgNo in (%@) AND r.removeTag = 0 AND (u.pcStatus = 'online' OR u.phoneStatus = 'online') ORDER BY r.ordId",orgNos];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql];
    
    NSMutableSet *userArray = [NSMutableSet set];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        [userArray addObject:user.userNo];
    }
    [rs close];
    
    return userArray.count;
}


//根据orgNo查询直属User数量,不包含老师信息
+(NSInteger)queryUserCountNotTeacherByOrgNos:(NSString *)orgNos database:(FMDatabase *)db
{
    //2016-11-6jc修改
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT * FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND u.userType = 2 AND r.orgNo in (%@)  AND r.removeTag = 0 ORDER BY r.ordId",orgNos];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql];
    
    NSMutableSet *userArray = [NSMutableSet set];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        [userArray addObject:user.userNo];
    }
    [rs close];
    
    return userArray.count;
}

//根据orgNo查询直属User在线人数数量,不包含老师信息
+(NSInteger)queryOnlineUserCountNotTeacherByOrgNos:(NSString *)orgNos database:(FMDatabase *)db
{
    //2016-11-6jc修改
    NSString *unitQuerySql = [NSString stringWithFormat:@"SELECT * FROM UserTreeRelation r,User u WHERE r.userNo = u.userNo AND u.userType = 2 AND r.orgNo in (%@)  AND r.removeTag = 0 AND (u.pcStatus = 'online' OR u.phoneStatus = 'online') ORDER BY r.ordId",orgNos];
    
    FMResultSet *rs = [db executeQuery:unitQuerySql];
    
    NSMutableSet *userArray = [NSMutableSet set];
    
    while ([rs next])
    {
        YNUser *user = [[YNUser alloc] init];
        if (![YNIMTool isEmptyDictionary:[rs resultDictionary]]) {
            [user setValuesForKeysWithDictionary:[rs resultDictionary]];
        }
        [userArray addObject:user.userNo];
    }
    [rs close];
    
    return userArray.count;
}


//删除表中所有数据
+ (BOOL)deleteAllUserTreeDataInDatabase:(FMDatabase *)db
{
    NSString *deleteAllUserTreeDataSql = [NSString stringWithFormat:@"DELETE FROM %@ ",[YNUserTreeRelation tableName]];
    
    BOOL result = [db executeUpdate:deleteAllUserTreeDataSql];
    
    return result;
}

//2016-9-9jc修改，逻辑删除表中所有数据
+ (BOOL)logicDeleteAllUserTreeDataInDatabase:(FMDatabase *)db
{
    NSString *updateUserTreeRelationSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = 1",
                                           [YNUserTreeRelation tableName],
                                           [YNUserTreeRelation removeTag]];
    
    BOOL result = [db executeUpdate:updateUserTreeRelationSql];
    return result;
}


@end
